﻿CREATE procedure [dbo].[sp_T_LayThongTinKiemKe_TheoKho]
	@dotKK int, @kho int
as
begin
select kk.*, vt.VatTu, vt.MaKyHieu,dv.DVT, t.Gia, k.Kho, TienKK = kk.SLKK*t.Gia
from tblKiemKe kk, tblTon t, tblVatTu vt, tblDVT dv, tblKho k
where kk.DotKiemKe = @dotKK and kk.ID_MatHang = t.ID_MatHang
and t.ID_Kho = k.ID_Kho
and t.ID_Kho = @kho
and t.ID_VatTu = vt.ID_VatTu and vt.ID_DVT = dv.ID_DVT
end
go
--==============================================================================================================
/* ***************************************************
	Người tạo: Thanh
	Ngày tạo: 25/1/2016
	Ngày sửa: 25/1/2016
	Nội dung: Thực hiện việc kiểm kê ngày 1/1 tự động	
**************************************************** */
CREATE procedure [dbo].[sp_T_KiemKe11_TheoKho]
(@kho int)
as
begin
	delete from tblKiemKe where DotKiemKe =1 and ID_MatHang in (select ID_MatHang from tblTon where ID_Kho = @kho)
	INSERT INTO tblKiemKe
		SELECT 1, ID_MatHang, t.SLKK11Old, SLTang=t.slNhapdk11,
			SLGiam =t.slXuatdk11, t.SLSS11, t.SLSS11, 0,0,
			SLCCL1=
			case 
				when t.ID_CCL =1 then t.SLSS11
				else 0 
			end,
			SLCCL2=
			case 
				when t.ID_CCL =2 then t.SLSS11
				else 0 
			end,
			SLCCL3a=
			case 
				when t.ID_CCL =3 then t.SLSS11
				else 0 
			end,
			SLCCL3b=
			case 
				when t.ID_CCL =4 then t.SLSS11
				else 0 
			end,
			0,0, t.SLSS11,0,0,null
		FROM tblTon t
		where t.ID_Kho = @kho
end
GO
--===========================================================================================================================
/* ***************************************************
	Người tạo: Thanh
	Ngày tạo: 25/1/2016
	Ngày sửa: 25/1/2016
	Nội dung: Thực hiện việc kiểm kê ngày 1/7 tự động	
*****************************************************/
create procedure [dbo].[sp_T_KiemKe17_TheoKho]
(@kho int)
as
begin
	delete from tblKiemKe where DotKiemKe=2 and ID_Mathang in (select ID_MatHang from tblTon where ID_Kho =@kho)
	INSERT INTO tblKiemKe
		SELECT 2, temp.ID_MatHang, temp.SLKK17Old, SLTang=temp.SLNhap17_11+temp.Nhap,
			SLGiam =temp.SLXuat17_11+temp.Xuat,
			temp.SLKK17Old +temp.SLNhap17_11+temp.Nhap -temp.SLXuat17_11 - temp.Xuat , temp.SLKK17Old +temp.SLNhap17_11+temp.Nhap -temp.SLXuat17_11 - temp.Xuat , 0,0,
			SLCCL1=
			case 
				when temp.ID_CCL =1 then temp.SLKK17Old +temp.SLNhap17_11+temp.Nhap -temp.SLXuat17_11 - temp.Xuat 
				else 0 
			end,
			SLCCL2=
			case 
				when temp.ID_CCL =2 then temp.SLKK17Old +temp.SLNhap17_11+temp.Nhap -temp.SLXuat17_11 - temp.Xuat 
				else 0 
			end,
			SLCCL3a=
			case 
				when temp.ID_CCL =3 then temp.SLKK17Old +temp.SLNhap17_11+temp.Nhap -temp.SLXuat17_11 - temp.Xuat 
				else 0 
			end,
			SLCCL3b=
			case 
				when temp.ID_CCL =4 then temp.SLKK17Old +temp.SLNhap17_11+temp.Nhap -temp.SLXuat17_11 - temp.Xuat 
				else 0 
			end,
			0,0,temp.SLKK17Old +temp.SLNhap17_11+temp.Nhap -temp.SLXuat17_11 - temp.Xuat ,0,0,null
		FROM 
		(
				SELECT t.ID_MatHang, t.SLKK17Old, t.Gia, t.ID_CCL, t.SLNhap17_11, t.SLXuat17_11, 
			sum(nx.Nhap) as Nhap, sum(nx.Xuat) as Xuat, (t.SLKK17Old + sum(nx.Nhap) - sum(nx.Xuat)) as Ton
				FROM 
				(
					select ID_MatHang, 0 as Nhap, 0 as Xuat, SLKK17Old as Ton
					from tblTon
					where ID_Kho = @kho
					union all 
					select ct.ID_MatHang, ct.SLKH as Nhap, 0 as Xuat, ct.SLKH as Ton
					from tblSLNX ct, tblDPNX p
					where ct.ID_DPNX= p.ID_DPNX and p.NX='N' and month(p.NgayPhieu)<7
					union all
					select ct.ID_MatHang, 0 as Nhap, ct.SLKH as Xuat, -ct.SLKH as Ton
					from tblSLNX ct, tblDPNX p
					where ct.ID_DPNX= p.ID_DPNX and p.NX='X' and month(p.NgayPhieu)<7
				) as nx , tblTon as t 
				where nx.ID_MatHang = t.ID_MatHang and t.ID_Kho = @kho
				group by t.ID_MatHang, t.SLKK17Old, t.SLNhap17_11, t.SlXuat17_11, t.Gia, t.ID_CCL
			) as temp
end

GO

